package com.medical.standard.dao;

import com.medical.standard.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExportDao {

    /**
     * 查询能力建设信息网站项目表数据
     */
    public List<Map<String, Object>> selectCapacityTableData(String standardCode, String projectName) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT i.project_name as projectName, s.standard_code as standardCode, i.project_num as projectNum ");
            sql.append("FROM inspection i ");
            sql.append("INNER JOIN standard s ON i.standard_id = s.id ");
            sql.append("WHERE 1=1 ");

            List<Object> params = new ArrayList<>();

            if (standardCode != null && !standardCode.isEmpty()) {
                sql.append("AND s.standard_code = ? ");
                params.add(standardCode);
            }

            if (projectName != null && !projectName.isEmpty()) {
                sql.append("AND i.project_name LIKE ? ");
                params.add("%" + projectName + "%");
            }

            sql.append("ORDER BY s.standard_code, i.project_num");

            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                resultList.add(row);
            }

        } catch (Exception e) {
            throw new RuntimeException("查询能力建设信息网站项目表数据失败", e);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return resultList;
    }

    /**
     * 查询认可检验检测能力表数据
     */
    public List<Map<String, Object>> selectInspectionCapacityData(String standardCode, String projectName) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT i.project_name as projectName, s.standard_name as standardName, ");
            sql.append("CONCAT(s.standard_code, ' ', i.project_num) as standardItem, ");
            sql.append("COALESCE(e.equipment_name, '待配置') as equipmentName ");
            sql.append("FROM inspection i ");
            sql.append("INNER JOIN standard s ON i.standard_id = s.id ");
            sql.append("LEFT JOIN equipment e ON i.inspection_id = e.inspection_num ");
            sql.append("WHERE 1=1 ");

            List<Object> params = new ArrayList<>();

            if (standardCode != null && !standardCode.isEmpty()) {
                sql.append("AND s.standard_code = ? ");
                params.add(standardCode);
            }

            if (projectName != null && !projectName.isEmpty()) {
                sql.append("AND i.project_name LIKE ? ");
                params.add("%" + projectName + "%");
            }

            sql.append("ORDER BY s.standard_code, i.project_num");

            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                resultList.add(row);
            }

        } catch (Exception e) {
            throw new RuntimeException("查询认可检验检测能力表数据失败", e);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return resultList;
    }

    /**
     * 查询操作规程表数据
     */
    public List<Map<String, Object>> selectOperationProcedureData(String standardCode, String projectName) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT s.standard_code as standardCode, s.standard_name as standardName, ");
            sql.append("i.project_num as projectNum, i.project_name as projectName, ");
            sql.append("i.project_requirement as projectRequirement, ");
            sql.append("COALESCE(e.equipment_name, '待配置') as equipment, ");
            sql.append("COALESCE(a.appendix_text, '详见标准正文') as itemMethod, ");
            sql.append("COALESCE('无', '') as note ");
            sql.append("FROM inspection i ");
            sql.append("INNER JOIN standard s ON i.standard_id = s.id ");
            sql.append("LEFT JOIN equipment e ON i.inspection_id = e.inspection_num ");
            sql.append("LEFT JOIN appendix a ON i.standard_id = a.standard_id ");
            sql.append("WHERE 1=1 ");

            List<Object> params = new ArrayList<>();

            if (standardCode != null && !standardCode.isEmpty()) {
                sql.append("AND s.standard_code = ? ");
                params.add(standardCode);
            }

            if (projectName != null && !projectName.isEmpty()) {
                sql.append("AND i.project_name LIKE ? ");
                params.add("%" + projectName + "%");
            }

            sql.append("ORDER BY s.standard_code, i.project_num");

            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                resultList.add(row);
            }

        } catch (Exception e) {
            throw new RuntimeException("查询操作规程表数据失败", e);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return resultList;
    }

    /**
     * 搜索标准内容
     */
    public List<Map<String, Object>> searchStandardContent(String keyword) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT i.project_name as projectName, i.project_requirement as projectRequirement, " +
                    "a.appendix_text as appendixContent, s.standard_code as standardCode, " +
                    "s.standard_name as standardName " +
                    "FROM inspection i " +
                    "INNER JOIN standard s ON i.standard_id = s.id " +
                    "LEFT JOIN appendix a ON i.standard_id = a.standard_id " +
                    "WHERE i.project_name LIKE ? OR i.project_requirement LIKE ? OR a.appendix_text LIKE ? " +
                    "ORDER BY s.standard_code, i.project_num " +
                    "LIMIT 50";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setString(2, "%" + keyword + "%");
            pstmt.setString(3, "%" + keyword + "%");

            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                resultList.add(row);
            }

        } catch (Exception e) {
            throw new RuntimeException("搜索标准内容失败", e);
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return resultList;
    }
}